package com.surprise.db.dao.join;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.surprise.db.dao.SimpleDAO;
import com.surprise.db.entity.join.SelectPMThingsMst001Entity;

/**
 * SelectPMThingsMst001.java
 * @author xuling
 * @since 2017年2月19日
 */
@Repository
public class SelectPMThingsMst001 extends SimpleDAO<SelectPMThingsMst001Entity> {
	
	/**
	 * 获取指定商场所有的那些事
	 * @param mallid 商场ID
	 * @param userid 用户ID
	 * @param limit limit信息
	 * @return 指定商场的那些事
	 */
	public List<SelectPMThingsMst001Entity> select(Long mallid, Long userid, String limit) {
		// 先获得关注用户的发布，再获得其他发布
		String sql = "SELECT subQuery1.id, subQuery1.userid, subQuery1.name, " +
					 " subQuery1.portrait, subQuery1.content, subQuery1.titleimg, subQuery1.addtime, " +
					 " subQuery1.address, subQuery1.mallName, " +
					 " subQuery1.likeCount, " +
					 " subQuery1.commentCount, " +
					 " subQuery1.shareCount, " +
					 " subQuery1.userLikeCount, " +
					 " subQuery1.userRelationCount from " +
					 " (select pm_things_mst.id, pm_things_mst.userid, user.username as name, " +
					 " user.portrait as portrait, pm_things_mst.content, pm_things_mst.titleimg, pm_things_mst.addtime, " +
					 " pm_things_mst.address, mall.name as mallName, " +
					 " COUNT(pm_things_like_mst.id) as likeCount, " +
					 " COUNT(pm_things_comment_mst.id) as commentCount, " +
					 " COUNT(pm_things_share_mst.id) as shareCount, " +
					 " COUNT(likeB.id) as userLikeCount, " +
					 " 1 as userRelationCount " +
					 " from pm_things_mst " +
					 " LEFT JOIN user on pm_things_mst.userid = user.id " +
					 " LEFT JOIN pm_things_like_mst on pm_things_mst.id = pm_things_like_mst.thingsid " +
					 " LEFT JOIN pm_things_like_mst likeB on pm_things_mst.id = likeB.thingsid and likeB.userid = ? and likeB.deleteflg = 0 " +
					 " LEFT JOIN pm_things_comment_mst on pm_things_mst.id = pm_things_comment_mst.thingsid " +
					 " LEFT JOIN pm_things_share_mst on pm_things_mst.id = pm_things_share_mst.thingsid and pm_things_share_mst.deleteflg = 0 " +
					 " LEFT JOIN mall on pm_things_mst.mallid = mall.id " +
					 " WHERE pm_things_mst.userid IN (SELECT user_relation.friendid FROM user_relation " +
					 " WHERE user_relation.userid = ? and user_relation.deleteflg = 0) " +
					 " and pm_things_mst.mallid = ? " +
					 " GROUP BY " +
					 " pm_things_mst.id, pm_things_mst.userid,  " +
					 " pm_things_mst.content, pm_things_mst.titleimg " +
					 " ORDER BY pm_things_mst.addtime desc " +
					 " ) as subQuery1 " +
					 " UNION ALL " +
					 " SELECT subQuery2.id, subQuery2.userid, subQuery2.name, " +
					 " subQuery2.portrait, subQuery2.content, subQuery2.titleimg, subQuery2.addtime, " +
					 " subQuery2.address, subQuery2.mallName, " + 
					 " subQuery2.likeCount, " +
					 " subQuery2.commentCount, " +
					 " subQuery2.shareCount, " +
					 " subQuery2.userLikeCount, " +
					 " subQuery2.userRelationCount from " +
					 " (select pm_things_mst.id, pm_things_mst.userid, user.username as name,  " +
					 " user.portrait as portrait, pm_things_mst.content, pm_things_mst.titleimg, pm_things_mst.addtime, " +
					 " pm_things_mst.address, mall.name as mallName,  " +
					 " COUNT(pm_things_like_mst.id) as likeCount, " +
					 " COUNT(pm_things_comment_mst.id) as commentCount, " +
					 " COUNT(pm_things_share_mst.id) as shareCount, " +
					 " COUNT(likeB.id) as userLikeCount, " +
					 " 0 as userRelationCount " +
					 " from pm_things_mst  " +
					 " LEFT JOIN user on pm_things_mst.userid = user.id  " +
					 " LEFT JOIN pm_things_like_mst on pm_things_mst.id = pm_things_like_mst.thingsid " +
					 " LEFT JOIN pm_things_like_mst likeB on pm_things_mst.id = likeB.thingsid and likeB.userid = ? and likeB.deleteflg = 0 " +
					 " LEFT JOIN pm_things_comment_mst on pm_things_mst.id = pm_things_comment_mst.thingsid " +
					 " LEFT JOIN pm_things_share_mst on pm_things_mst.id = pm_things_share_mst.thingsid and pm_things_share_mst.deleteflg = 0 " +
					 " LEFT JOIN mall on pm_things_mst.mallid = mall.id " +
					 " WHERE pm_things_mst.userid NOT IN (SELECT user_relation.friendid FROM user_relation " +
					 " WHERE user_relation.userid = ? and user_relation.deleteflg = 0) " +
					 " and pm_things_mst.mallid = ? " +
					 " GROUP BY " +
					 " pm_things_mst.id, pm_things_mst.userid,  " +
					 " pm_things_mst.content, pm_things_mst.titleimg " +
					 " ORDER BY pm_things_mst.addtime desc " +
					 " ) as subQuery2 " +
					 limit;
		return super.findBySql(sql, userid, userid, mallid, userid, userid, mallid);
	}
}
